﻿using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Text;
using XD.Core;

namespace TestPostgis
{
    class Program
    {
        static void Main(string[] args)
        {
            List<Track> tracks = new List<Track>();
            
            try
            {
                #region 从Sqlite中读取轨迹数据

                //Sqlite数据库路径
                string sqlitePath = Path.Combine(Environment.CurrentDirectory, "track.db");

                //数据表名称
                string sqliteTb = "T_1140204000334";

                using (SqliteDB sqlite = new SqliteDB(sqlitePath))
                {
                    sqlite.Open();
                    DataTable dt = sqlite.ExecuteSQL("select * from " + sqliteTb + ";");
                    if (dt != null && dt.Rows.Count > 0)
                    {
                        for (int i = 0; i < dt.Rows.Count; i++)
                        {
                            tracks.Add(new Track()
                            {
                                B = Convert.ToDouble(dt.Rows[i]["B"]) * 180 / Math.PI,
                                L = Convert.ToDouble(dt.Rows[i]["L"]) * 180 / Math.PI,
                                H = Convert.ToSingle(dt.Rows[i]["H"]),
                                Azi = Convert.ToSingle(dt.Rows[i]["Azi"]),
                                AntH = Convert.ToSingle(dt.Rows[i]["AntH"]),
                                UTCTime = Convert.ToDateTime(dt.Rows[i]["UTCTime"]),
                                Hdop = Convert.ToSingle(dt.Rows[i]["Hdop"]),
                                SolType = Convert.ToInt32(Convert.ToByte(dt.Rows[i]["SolType"]))
                            });
                        }
                    }
                }

                Console.WriteLine("Sqlite中的轨迹记录数为：" + tracks.Count);
                Console.WriteLine("---------------------------------------------------------------------");

                #endregion

                #region 向PostgreSQL中存入轨迹数据

                using (PostgresqlDB postgresql = new PostgresqlDB("localhost", "5432", "postgres", "123456", "test"))
                {
                    postgresql.Open();

                    string tableName = "t_1_1_20150519103523";

                    StringBuilder sql = new StringBuilder();

                    #region 写入数据

                    //删除数据表
                    if (postgresql.QueryTableIsExist(tableName))
                    {
                        sql.Clear();
                        sql.AppendLine("drop table " + tableName + ";");
                        postgresql.ExecuteNonQuery(sql.ToString());
                    }

                    postgresql.BeginTransaction();

                    try
                    {
                        //创建数据表
                        sql.Clear();
                        sql.AppendLine("create table " + tableName + "(");
                        sql.AppendLine("id serial not null,");
                        sql.AppendLine("coord geometry(point, 4326) not null,");
                        sql.AppendLine("h real not null,");
                        sql.AppendLine("azi real not null,");
                        sql.AppendLine("ant_h real not null,");
                        sql.AppendLine("utc_time timestamp with time zone not null,");
                        sql.AppendLine("hdop real not null,");
                        sql.AppendLine("sol_type integer not null,");
                        sql.AppendLine("constraint " + tableName + "_pkey primary key (id)");
                        sql.AppendLine(");");

                        postgresql.ExecuteNonQuery(sql.ToString());

                        //插入记录
                        for (int i = 0; i < tracks.Count; i++)
                        {
                            sql.Clear();
                            sql.AppendLine("insert into " + tableName + "(coord,h,azi,ant_h,utc_time,hdop,sol_type) values(");
                            sql.AppendLine(string.Format("ST_GeomFromText('point({0} {1})',4326),", tracks[i].B, tracks[i].L));
                            sql.AppendLine(tracks[i].H + ",");
                            sql.AppendLine(tracks[i].Azi + ",");
                            sql.AppendLine(tracks[i].AntH + ",");
                            sql.AppendLine("'" + tracks[i].UTCTime.ToString("yyyy-MM-dd HH:mm:ss") + "',");
                            sql.AppendLine(tracks[i].Hdop + ",");
                            sql.AppendLine(tracks[i].SolType.ToString());
                            sql.AppendLine(");");

                            postgresql.ExecuteNonQuery(sql.ToString());
                        }

                        postgresql.Commit();

                        Console.WriteLine("写入postgresql成功！");
                        Console.WriteLine("---------------------------------------------------------------------");
                    }
                    catch (Exception ex)
                    {
                        postgresql.Rollback();
                        Console.WriteLine(ex.Message);
                        Console.WriteLine(ex.StackTrace);
                    }

                    postgresql.EndTransaction();

                    #endregion

                    #region 读取数据

                    tracks.Clear();

                    DataTable dt = postgresql.ExecuteQuery("select st_x(coord) as b, st_y(coord) as l, h, azi, ant_h, utc_time, hdop, sol_type from " + tableName + ";");
                    if (dt != null && dt.Rows.Count > 0)
                    {                        
                        for (int i = 0; i < dt.Rows.Count; i++)
                        {
                            tracks.Add(new Track() { 
                                B = Convert.ToDouble(dt.Rows[i]["b"]),
                                L = Convert.ToDouble(dt.Rows[i]["l"]),
                                H = Convert.ToSingle(dt.Rows[i]["h"]),
                                Azi = Convert.ToSingle(dt.Rows[i]["azi"]),
                                AntH = Convert.ToSingle(dt.Rows[i]["ant_h"]),
                                UTCTime = Convert.ToDateTime(dt.Rows[i]["utc_time"]),
                                Hdop = Convert.ToSingle(dt.Rows[i]["hdop"]),
                                SolType = Convert.ToInt32(dt.Rows[i]["sol_type"])
                            });
                        }
                    }
                    Console.WriteLine("读取postgresql中的数据成功！记录数为：" + tracks.Count);
                    Console.WriteLine("---------------------------------------------------------------------");

                    #endregion

                    #region 轨迹分析
                    
                    //轨迹总长度
                    double dist = 0;
                    DataTable dtDistance = postgresql.ExecuteQuery("select st_length(st_makeline(st_transform(coord, 4504))) from " + tableName + ";");
                    if (dtDistance != null && dtDistance.Rows.Count > 0)
                    {
                        dist = Convert.ToDouble(dtDistance.Rows[0][0]);
                        Console.WriteLine("轨迹总长度：" + dist + " 米");
                        Console.WriteLine("---------------------------------------------------------------------");
                    }

                    //轨迹总时间
                    double seconds = 0;
                    DataTable dtDateTime = postgresql.ExecuteQuery("select max(utc_time), min(utc_time) from " + tableName + ";");
                    if (dtDateTime != null && dtDateTime.Rows.Count > 0)
                    {
                        DateTime max = Convert.ToDateTime(dtDateTime.Rows[0][0]);
                        DateTime min = Convert.ToDateTime(dtDateTime.Rows[0][1]);
                        seconds = (max - min).TotalSeconds;
                        Console.WriteLine("轨迹总时间：" + seconds + " 秒");
                        Console.WriteLine("---------------------------------------------------------------------");
                    }

                    //轨迹平均速度
                    if (seconds != 0)
                    {
                        Console.WriteLine("轨迹平均速度：" + dist / seconds + " 米/秒");
                        Console.WriteLine("---------------------------------------------------------------------");
                    }

                    //轨迹瞬时速度
                    DataTable dtDelta = postgresql.ExecuteQuery("select st_distance(st_transform(p1.coord,4504),st_transform(p2.coord,4504)) as delta_d, extract(epoch from p1.utc_time-p2.utc_time) as delta_t from " + tableName + " as p1, " + tableName + " as p2 where p1.id = p2.id+1;");
                    if (dtDelta != null && dtDelta.Rows.Count > 0)
                    {
                        double delta_d = Convert.ToDouble(dtDelta.Rows[dtDelta.Rows.Count - 1]["delta_d"]);
                        double delta_t = Convert.ToDouble(dtDelta.Rows[dtDelta.Rows.Count - 1]["delta_t"]);
                        if (delta_t != 0)
                        {
                            Console.WriteLine("最后一个点的分析结果为：\r\n    瞬时位移：" + delta_d + " 米" +
                                "\r\n    时间间隔：" + delta_t + " 秒" +
                                "\r\n    瞬时速度：" + delta_d / delta_t + " 米/秒");
                            Console.WriteLine("---------------------------------------------------------------------");
                        }
                    }

                    #endregion
                }

                #endregion
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                Console.WriteLine(ex.StackTrace);
            }
            Console.Read();
        }
    }
}
